using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using DTO;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class MATHANG_DAL
    {
        private SqlConnection cn = null;
        public MATHANG_DAL()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"]
                  .ConnectionString);
            cn.Open();
        }
  
	
        public int insert(MatHang_DTO mh)
        {   SqlCommand cmd = new SqlCommand("MATHANG_INSERT", cn);
            cmd.CommandType = CommandType.StoredProcedure;
          
            cmd.Parameters.AddWithValue("@MAKHUVUC", mh.MaKhuVuc);
            cmd.Parameters.AddWithValue("@TENMH", mh.TenMH);
            cmd.Parameters.AddWithValue("@MALOAI", mh.MaLoai);
            cmd.Parameters.AddWithValue("@MADN", mh.MaDN);
            cmd.Parameters.AddWithValue("@MOTA", mh.MoTa);
            cmd.Parameters.AddWithValue("@GIABAN", mh.GiaBan);
            cmd.Parameters.AddWithValue("@TILEGIAM", mh.TiLeGiam);
            cmd.Parameters.AddWithValue("@PHANTRAM_CHIETKHAU", mh.PhanTram_ChietKhau);
            cmd.Parameters.AddWithValue("@THOIGIAN_BATDAU", mh.ThoiGian_BatDau);
            cmd.Parameters.AddWithValue("@THOIGIAN_KETTHUC", mh.ThoiGian_KetThuc);
            cmd.Parameters.AddWithValue("@SOLUONGPHIEUBAN", mh.SoLuongPhieuBan);
            cmd.Parameters.AddWithValue("@SOLUONGPHIEUCONLAI", mh.SoLuongPhieuConLai);
            cmd.Parameters.AddWithValue("@MATINHTRANG_SP", mh.MaTinhTrang_SP);
            cmd.Parameters.AddWithValue("@PHIGIAOHANGTANNOI", mh.PhiGiaoHangTanNoi);
            cmd.Parameters.AddWithValue("@HINHMINHHOA", mh.HinhMinhHoa);
            cmd.Parameters.AddWithValue("@LUOTXEM", mh.LuotXem);
        
            //cmd.ExecuteNonQuery();
            return int.Parse(cmd.ExecuteScalar().ToString());
        }
        public void update(MatHang_DTO mh)
        {
            SqlCommand cmd = new SqlCommand("MATHANG_UPDATE", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MAMH", mh.MaMH);
          
            cmd.Parameters.AddWithValue("@MAKHUVUC", mh.MaKhuVuc);
            cmd.Parameters.AddWithValue("@TENMH", mh.TenMH);
            cmd.Parameters.AddWithValue("@MALOAI", mh.MaLoai);
            cmd.Parameters.AddWithValue("@MADN", mh.MaDN);
            cmd.Parameters.AddWithValue("@MOTA", mh.MoTa);
            cmd.Parameters.AddWithValue("@GIABAN", mh.GiaBan);
            cmd.Parameters.AddWithValue("@TILEGIAM", mh.TiLeGiam);
            cmd.Parameters.AddWithValue("@PHANTRAM_CHIETKHAU", mh.PhanTram_ChietKhau);
            cmd.Parameters.AddWithValue("@THOIGIAN_BATDAU", mh.ThoiGian_BatDau);
            cmd.Parameters.AddWithValue("@THOIGIAN_KETTHUC", mh.ThoiGian_KetThuc);
            cmd.Parameters.AddWithValue("@SOLUONGPHIEUBAN", mh.SoLuongPhieuBan);
            cmd.Parameters.AddWithValue("@SOLUONGPHIEUCONLAI", mh.SoLuongPhieuConLai);
            cmd.Parameters.AddWithValue("@MATINHTRANG_SP", mh.MaTinhTrang_SP);
            //cmd.Parameters.AddWithValue("@PHIGIAOHANGTANNOI", mh.PhiGiaoHangTanNoi);
            cmd.Parameters.AddWithValue("@HINHMINHHOA", mh.HinhMinhHoa);
            cmd.Parameters.AddWithValue("@LUOTXEM", mh.LuotXem);
           
            cmd.ExecuteNonQuery();
        }
        
        public void updatemota(MatHang_DTO mamh)
        {
            SqlCommand cmd = new SqlCommand("MATHANG_UPDATE_mota", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MAMH", mamh.MaMH);

          
            cmd.Parameters.AddWithValue("@MOTA", mamh.MoTa);
           

            cmd.ExecuteNonQuery();
        }
         public void updateTINHTRANGHETHAN(MatHang_DTO mamh)
        {
            SqlCommand cmd = new SqlCommand("dbo.StoredProcedure1", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MAMH", mamh.MaMH);

          
            cmd.Parameters.AddWithValue("@MATINHTRANG_SP", mamh.MaTinhTrang_SP);
           

            cmd.ExecuteNonQuery();
        }
      
        public void updateSOLUONGMATHANG(MatHang_DTO mahang)
        {

            SqlCommand cmd = new SqlCommand("MATHANG_UPDATE_soluong", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MAMH", mahang.MaMH);


            cmd.Parameters.AddWithValue("@SOLUONGPHIEUCONLAI", mahang.SoLuongPhieuConLai);


            cmd.ExecuteNonQuery();
        }
        public void delete(int mamh)
        {
            SqlCommand cmd = new SqlCommand("MATHANG_DELETE",cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@MAMH",mamh);
            cmd.ExecuteNonQuery();
        }
       
        public MatHangColection Get(String where, int pageIndex, int pageSize, string orderBy, string orderDirection, out int totalRecords)
        {
            SqlCommand cm = new SqlCommand("MATHANG_GET", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@WHERE", where);
            cm.Parameters.AddWithValue("@PageIndex", pageIndex);
            cm.Parameters.AddWithValue("@PAGESIZE", pageSize);
            cm.Parameters.AddWithValue("@ORDERBY", orderBy);
            cm.Parameters.AddWithValue("@ORDERDIRECTION", orderDirection);

            //cm.Parameters .Add (new SqlParameter ("@TOTALRECORD",DbType.Int32)).Direction=ParameterDirection.Output 
            cm.Parameters.Add(new SqlParameter("@TOTALRECORD", DbType.Int32)).Direction = ParameterDirection.Output;
            SqlDataReader dr = cm.ExecuteReader();
            MatHangColection MHCollection = new MatHangColection();
            while (dr.Read())
            {   MatHang_DTO mathang = new MatHang_DTO();
                mathang.MaMH = int.Parse(dr.GetValue(0).ToString());
                //mathang.MaNV = int.Parse(dr.GetValue(1).ToString());
                mathang.MaKhuVuc = int.Parse(dr.GetValue(1).ToString());
                mathang.TenMH = dr.GetValue(2).ToString();
                mathang.MaLoai = int.Parse(dr.GetValue(3).ToString());
                mathang.MaDN = int.Parse(dr.GetValue(4).ToString());
                mathang.MoTa = dr.GetValue(5).ToString();
                mathang.GiaBan = float.Parse(dr.GetValue(6).ToString());
                mathang.TiLeGiam= float.Parse(dr.GetValue(7).ToString());
                mathang.PhanTram_ChietKhau= float.Parse(dr.GetValue(8).ToString());
                mathang.ThoiGian_BatDau = DateTime.Parse(dr.GetValue(9).ToString());
                mathang.ThoiGian_KetThuc = DateTime.Parse(dr.GetValue(10).ToString());
                mathang.SoLuongPhieuBan= int.Parse(dr.GetValue(11).ToString());
                mathang.SoLuongPhieuConLai= int.Parse(dr.GetValue(12).ToString());
                mathang.MaTinhTrang_SP= int.Parse(dr.GetValue(13).ToString());
                mathang.PhiGiaoHangTanNoi= int.Parse(dr.GetValue(14).ToString());
                mathang.HinhMinhHoa= dr.GetValue(15).ToString();
                mathang.LuotXem= int.Parse(dr.GetValue(16).ToString());
            
                //mathang.ThoiGian_BatDau = DateTime.Parse(dr.GetValue(10).ToString ());
                MHCollection.Add(mathang);
            }
            dr.Close();
            totalRecords = int.Parse(cm.Parameters["@TOTALRECORD"].Value.ToString());
            return MHCollection;

        }

    }
}
